package com.zzxz.practicaltrainingsys.mapper;

import com.zzxz.practicaltrainingsys.entity.Homeworkpublish;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface HomeworkpublishMapper {

    //查询某个项目发布作业信息
    @Select("select * from homeworkpublish where project_id= #{id}")
    List<Homeworkpublish> getHomeworkpublishById(Integer id);

    //根据homework_id查找作业详细
    @Select("select * from homeworkpublish where homework_id= #{id}")
    Homeworkpublish getDetailHomeworkpublishById(Integer id);

    //新发布作业
    @Insert("INSERT INTO homeworkpublish " +
            "(teacher_id, homework_type, homework_category, homework_name, " +
            "homework_introduction, publish_time, homework_start, homework_end, project_id) " +
            "VALUES " +
            "(#{teacher_id}, #{homework_type}, #{homework_category}, #{homework_name}, " +
            "#{homework_introduction}, #{publish_time}, #{homework_start}, #{homework_end}, #{project_id})")
    int insertHomeworkpublish(Homeworkpublish homeworkpublish);

    //删除发布的作业
    @Delete("DELETE FROM homeworkpublish WHERE homework_id = #{id}")
    int deleteHomeworkpublishById(Integer id);

    //获取所有发布作业类型
    @Select("select DISTINCT homework_type from homeworkpublish")
    public List<String> getHomeworkpublishTypes();

    //获取所有发布作业分类
    @Select("select DISTINCT homework_category from homeworkpublish")
    public List<String> getHomeworkpublishCategory();

    //管理员端查询所有发布作业（包含项目名称和上传老师名称）- 修复homework_id缺失问题
    @Select("SELECT hp.homework_id, p.project_name, t.teacher_name, hp.homework_type, hp.homework_category, " +
            "hp.homework_name, hp.homework_introduction, hp.publish_time, " +
            "hp.homework_start, hp.homework_end, hp.project_id " +
            "FROM homeworkpublish hp " +
            "JOIN project p ON hp.project_id = p.project_id " +
            "JOIN teacher t ON hp.teacher_id = t.teacher_id " +
            "ORDER BY hp.publish_time DESC")
    List<Map<String, Object>> getAllHomeworkpublish();

    //查询发布作业列表
    @Select("select * from homeworkpublish")
    public List<Homeworkpublish> getHomeworkpublishInfo();

    //获取所有发布作业名称
    @Select("select DISTINCT homework_name from homeworkpublish")
    public List<String> getHomeworkpublishName();

    // 获取所有作业列表
    @Select("SELECT * FROM homeworkpublish")
    List<Homeworkpublish> findAll();

    // 根据ID获取作业详情
    @Select("SELECT * FROM homeworkpublish WHERE homework_id = #{homeworkId}")
    Homeworkpublish findById(@Param("homeworkId") Integer homeworkId);

    // 修复：获取小组作业完成情况 - 根据项目ID查找所有参与的小组
    @Select("SELECT t.team_id, t.team_name, t.leader_id, t.group_id," +
            "s.student_name as leader_name, " +
            "CASE WHEN hf.finish_id IS NOT NULL THEN true ELSE false END as finished, " +
            "hf.homework_marks as score " +
            "FROM team t " +
            "JOIN student s ON t.leader_id = s.student_id " +
            "LEFT JOIN homeworkfinish hf ON t.leader_id = hf.student_id AND hf.homework_id = #{homeworkId} " +
            "WHERE t.project_id = #{projectId} " +
            "ORDER BY t.team_name")
    List<Map<String, Object>> findGroupCompletion(@Param("homeworkId") Integer homeworkId,
                                                  @Param("projectId") Integer projectId);

    // 获取个人作业完成情况
    @Select("SELECT s.student_id, s.student_name, " +
            "CASE WHEN hf.finish_id IS NOT NULL THEN true ELSE false END as finished, " +
            "hf.homework_marks as score, " +
            "t.group_id as group_id " +
            "FROM student s " +
            "LEFT JOIN homeworkfinish hf ON s.student_id = hf.student_id AND hf.homework_id = #{homeworkId} " +
            "LEFT JOIN member m ON s.student_id = m.member_id " +
            "LEFT JOIN team t ON m.team_id = t.team_id " +
            "WHERE s.school_id = #{schoolId} AND s.college_id = #{collegeId} " +
            "AND s.major_id = #{majorId} AND s.grade_id = #{gradeId}")
    List<Map<String, Object>> findIndividualCompletion(@Param("homeworkId") Integer homeworkId,
                                                       @Param("schoolId") Integer schoolId,
                                                       @Param("collegeId") Integer collegeId,
                                                       @Param("majorId") Integer majorId,
                                                       @Param("gradeId") Integer gradeId);
    // 修复：获取小组成员详情 - 使用UNION查询组长、副组长和普通成员
    @Select("SELECT student_id, student_name, finished, score, role FROM (" +
            "  SELECT t.leader_id as student_id, s1.student_name, " +
            "         CASE WHEN hf1.finish_id IS NOT NULL THEN true ELSE false END as finished, " +
            "         hf1.homework_marks as score, '组长' as role " +
            "  FROM team t " +
            "  JOIN student s1 ON t.leader_id = s1.student_id " +
            "  LEFT JOIN homeworkfinish hf1 ON t.leader_id = hf1.student_id AND hf1.homework_id = #{homeworkId} " +
            "  WHERE t.team_id = #{teamId} " +
            "  UNION ALL " +
            "  SELECT t.viceleader_id as student_id, s2.student_name, " +
            "         CASE WHEN hf2.finish_id IS NOT NULL THEN true ELSE false END as finished, " +
            "         hf2.homework_marks as score, '副组长' as role " +
            "  FROM team t " +
            "  JOIN student s2 ON t.viceleader_id = s2.student_id " +
            "  LEFT JOIN homeworkfinish hf2 ON t.viceleader_id = hf2.student_id AND hf2.homework_id = #{homeworkId} " +
            "  WHERE t.team_id = #{teamId} AND t.viceleader_id IS NOT NULL " +
            "  UNION ALL " +
            "  SELECT m.student_id, s3.student_name, " +
            "         CASE WHEN hf3.finish_id IS NOT NULL THEN true ELSE false END as finished, " +
            "         hf3.homework_marks as score, '成员' as role " +
            "  FROM member m " +
            "  JOIN student s3 ON m.student_id = s3.student_id " +
            "  LEFT JOIN homeworkfinish hf3 ON m.student_id = hf3.student_id AND hf3.homework_id = #{homeworkId} " +
            "  WHERE m.team_id = #{teamId} " +
            "  AND m.student_id NOT IN (" +
            "    SELECT t.leader_id FROM team t WHERE t.team_id = #{teamId}" +
            "    UNION" +
            "    SELECT t.viceleader_id FROM team t WHERE t.team_id = #{teamId} AND t.viceleader_id IS NOT NULL" +
            "  )" +
            ") all_members " +
            "ORDER BY CASE role WHEN '组长' THEN 1 WHEN '副组长' THEN 2 ELSE 3 END, student_name")
    List<Map<String, Object>> findTeamMembers(@Param("teamId") Integer teamId,
                                              @Param("homeworkId") Integer homeworkId);
    // 获取项目对应的学校、学院、专业、年级信息
    @Select("SELECT school_id, college_id, major_id, grade_id FROM project WHERE project_id = #{projectId}")
    Map<String, Object> findProjectInfo(@Param("projectId") Integer projectId);

    // 获取学生参与项目的作业信息（修复：添加具体SQL实现）
    @Select("SELECT hp.homework_id, hp.homework_name, hp.homework_introduction, " +
            "hp.homework_type, hp.homework_category, hp.publish_time, " +
            "hp.homework_start, hp.homework_end, p.project_name, p.project_id, " +
            "t.teacher_name, " +
            "CASE WHEN hf.finish_id IS NOT NULL THEN 1 ELSE 0 END as is_finished, " +
            "hf.finish_time, hf.finish_content,hf.homework_marks " +
            "FROM homeworkpublish hp " +
            "JOIN project p ON hp.project_id = p.project_id " +
            "JOIN teacher t ON hp.teacher_id = t.teacher_id " +
            "LEFT JOIN homeworkfinish hf ON hp.homework_id = hf.homework_id AND hf.student_id = #{studentId} " +
            "WHERE hp.project_id IN (" +
            "    SELECT DISTINCT p.project_id FROM project p " +
            "    JOIN student s ON (s.school_id = p.school_id AND s.college_id = p.college_id " +
            "                      AND s.major_id = p.major_id AND s.grade_id = p.grade_id) " +
            "    WHERE s.student_id = #{studentId}" +
            ") " +
            "ORDER BY hp.publish_time DESC")
    List<Map<String, Object>> getStudentHomeworks(@Param("studentId") Integer studentId);

    // 更新作业发布信息
    @Update("UPDATE homeworkpublish SET " +
            "teacher_id = #{teacher_id}, " +
            "homework_type = #{homework_type}, " +
            "homework_category = #{homework_category}, " +
            "homework_name = #{homework_name}, " +
            "homework_introduction = #{homework_introduction}, " +
            "homework_start = #{homework_start}, " +
            "homework_end = #{homework_end}, " +
            "project_id = #{project_id} " +
            "WHERE homework_id = #{homework_id}")
    int updateHomeworkpublish(Homeworkpublish homeworkpublish);
    //根据老师项目找作业
    @Select("SELECT hp.homework_id, hp.homework_name, hp.homework_introduction, " +
            "hp.homework_type, hp.homework_category, hp.publish_time, " +
            "hp.homework_start, hp.homework_end, hp.teacher_id, hp.project_id, " +
            "p.project_name, t.teacher_name " +
            "FROM homeworkpublish hp " +
            "JOIN project p ON hp.project_id = p.project_id " +
            "JOIN teacher t ON hp.teacher_id = t.teacher_id " +
            "WHERE hp.teacher_id = #{teacherId} AND hp.project_id = #{projectId} " +
            "ORDER BY hp.publish_time DESC")
    List<Map<String, Object>> getHomeworkByTeacherAndProject(@Param("teacherId") Integer teacherId,
                                                             @Param("projectId") Integer projectId);
}